<?php

/**
 * School filter form.
 *
 * @package    sf_sandbox
 * @subpackage filter
 * @author     Your name here
 * @version    SVN: $Id: sfDoctrineFormFilterTemplate.php 23810 2009-11-12 11:07:44Z Kris.Wallsmith $
 */
class SchoolFormFilter extends BaseSchoolFormFilter {

    public function configure() {
        unset($this['intg_id'], $this['level'], $this['fdate'], $this['build'], $this['type'], $this['dismiss']
                , $this['office_number'], $this['email'], $this['phone'], $this['region'], $this['street']);

        $this->getWidget('sector_intg_id')->setOption('key_method', 'getIntgId');
        $this->getValidator('sector_intg_id')->setOption('column', 'intg_id');
    }

    public function doBuildQueryForReport($values) {
        
        if ($values['trainer_id'] != '') {
            $newq = Doctrine_Query::create()
                    ->select('t.id, concat(t.f_name," ",t.s_name," ",t.t_name," ",t.l_name) as teacher_name, s.name')
                    ->from('Teacher t')
                    ->leftJoin('t.Program p')
                    ->leftJoin('t.School s')
                    ->orderBy('s.name ASC')
                    ->where('p.trainer_id = ?', $values['trainer_id']);
            return $newq;
        }

        $and = '';
        $and_prog = '';
        $join = '';
        $last_and = '';

        if (isset($values['code_target_list']) && $values['code_target_list'] != '') {
            $targets = implode(',', $values['code_target_list']);
            $join = 'INNER JOIN kts_link_program_target targets ON targets.program_id = prog.id AND targets.code_target_id IN(' . $targets . ')';
        }

        if (isset($values['code_sector_list']) && $values['code_sector_list'] != '') {
            $sectors = implode(',', $values['code_sector_list']);
            //$join = 'INNER JOIN kts_link_program_sector sectors ON sectors.program_id = prog.id';
            $last_and = 'AND sector.intg_id IN(' . $sectors . ')';
            
        }

        if ($values['program_code']['text'] != '')
            $and_prog.= ' AND prog.program_code = "' . $values['program_code']['text'] . '"';

        if ($values['location_of_holding'] != '')
            $and_prog.= ' AND prog.location_of_holding = ' . $values['location_of_holding'];

        if ($values['is_manager'] != '')
            $and.= ' AND t.is_manager = ' . $values['is_manager'];

        if ($values['intg_identity'] != '')
            $and.= ' AND t.intg_identity = ' . $values['intg_identity'];

        if ($values['school_intg_id'] != '')
            $and.= ' AND s.intg_id = ' . $values['school_intg_id'];

        $query = <<<EOQ
SELECT 
  s.name as school_name,
  sector.name as sector_name,
  (SELECT 
    COUNT(t.id) 
  FROM
    kts_teacher t,
    kts_school s2 
  WHERE s2.intg_id = t.school_intg_id 
    AND s2.intg_id = s.`intg_id` $and
  GROUP BY s2.id) AS teacher_count,
  COUNT(p.teacher_id) AS assignee_count,
  COUNT(p.teacher_id) / 
  (SELECT 
    COUNT(t.id) 
  FROM
    kts_teacher t,
    kts_school s2 
  WHERE s2.intg_id = t.school_intg_id 
    AND s2.intg_id = s.intg_id $and
  GROUP BY s2.id) * 100 AS precentage
FROM
  kts_school s 
  INNER JOIN kts_teacher t 
    ON t.school_intg_id = s.intg_id 
  INNER JOIN kts_link_teacher_program p 
    ON p.teacher_id = t.intg_identity 
  INNER JOIN kts_program prog
    ON prog.id = p.program_id
  INNER JOIN kts_code_sector sector
    ON s.sector_intg_id = sector.intg_id
  $join
  WHERE 1
  AND p.assign_status = 2 $and $and_prog $last_and
GROUP BY s.intg_id
ORDER BY s.name ASC
EOQ;
//echo $query;exit;
        $q = Doctrine_Manager::getInstance()->getCurrentConnection();
//        echo '<pre>';
//        print_r($query);
//        echo '</pre>';
//        exit;
        $result = $q->execute($query);

        return $result;
    }

}
